package com.far.permissions.tools;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import lombok.SneakyThrows;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import org.springframework.expression.EvaluationContext;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.SimpleEvaluationContext;

import java.util.*;
import java.util.stream.Collectors;

public class SqlParserTools {

    public static final String TMP_SEGMENT = "1 = 1";
    public static final String AND_SEGMENT = " AND ";

    /**
     * 解析sql语句
     * @param unknownTypeSql
     * @return
     * @throws JSQLParserException
     */
    public static Statement parseSql(String unknownTypeSql) throws JSQLParserException {
        return CCJSqlParserUtil.parse(unknownTypeSql);
    }

    /**
     * 获取sql包含的表名和表别名
     * @param select
     * @return
     */
    public static Map<String, String> getTablesWithAlias(PlainSelect select) {
        Map<String, String> tables = new HashMap<>();
        Table fromTable = (Table) select.getFromItem();
        String fromAliasName = null;
        if (fromTable.getAlias() != null) {
            fromAliasName = fromTable.getAlias().getName();
        }
        tables.put(fromTable.getName(), fromAliasName);
        List<Join> joins = select.getJoins();
        if (joins != null) {
            for (Join join : joins) {
                Table joinTable = (Table) join.getRightItem();
                String joinAliasName = null;
                if (fromTable.getAlias() != null) {
                    joinAliasName = fromTable.getAlias().getName();
                }
                tables.put(joinTable.getName(), joinAliasName);
            }
        }
        return tables;
    }

    /**
     * 获取sql包含的表名和表别名
     * @param sql
     * @return
     */
    public static Map<String, String> getTablesWithAlias(String sql) throws JSQLParserException {
        Select select = parseSelectSql(sql);
        PlainSelect selectBody = (PlainSelect) select.getSelectBody();
        return getTablesWithAlias(selectBody);
    }

    /**
     * 解析sql为查询语句
     * @param selectSql
     * @return
     * @throws JSQLParserException
     */
    public static Select parseSelectSql(String selectSql) throws JSQLParserException {
        Statement statement = parseSql(selectSql);
        if (statement instanceof Select) {
            return (Select) statement;
        }
        throw new RuntimeException("type not match");
    }

    /**
     * 解析sql为插入语句
     * @param insertSql
     * @return
     * @throws JSQLParserException
     */
    public static Insert parseInsertSql(String insertSql) throws JSQLParserException {
        Statement statement = parseSql(insertSql);
        if (statement instanceof Insert) {
            return (Insert) statement;
        }
        throw new RuntimeException("type not match");
    }

    /**
     * 解析sql为更新语句
     * @param updateSql
     * @return
     * @throws JSQLParserException
     */
    public static Update parseUpdateSql(String updateSql) throws JSQLParserException {
        Statement statement = parseSql(updateSql);
        if (statement instanceof Update) {
            return (Update) statement;
        }
        throw new RuntimeException("type not match");
    }

    /**
     * 解析sql为删除语句
     * @param deleteSql
     * @return
     * @throws JSQLParserException
     */
    public static Delete parseDeleteSql(String deleteSql) throws JSQLParserException {
        Statement statement = parseSql(deleteSql);
        if (statement instanceof Delete) {
            return (Delete) statement;
        }
        throw new RuntimeException("type not match");
    }

    /**
     * 生成where条件片段
     * @param column 字段名
     * @param fillValue 填充值
     * @param expression 表达式
     * @return
     */
    @SneakyThrows
    public static String generateSegment(String column, Object fillValue, String expression) {
        ExpressionParser parser = new SpelExpressionParser();
        EvaluationContext context = SimpleEvaluationContext.forReadWriteDataBinding().build();
        context.setVariable("param", fillValue);
        context.setVariable("column", column);
        context.setVariable("parseParam",
                SqlParserTools.class.getDeclaredMethod("parseParam", Object.class));
        return parser.parseExpression(expression).getValue(context, String.class);
    }

    /**
     * 连接旧的where条件和新的where条件
     * @param oldWhere
     * @param newWhere
     * @return
     * @throws JSQLParserException
     */
    public static Expression linkExpressions(Expression oldWhere, Expression newWhere) throws JSQLParserException {
        return linkExpressions(oldWhere.toString(), newWhere.toString());
    }

    /**
     * 连接旧的where条件和新的where条件
     * @param oldWhere
     * @param newWhere
     * @return
     * @throws JSQLParserException
     */
    public static Expression linkExpressions(String oldWhere, String newWhere) throws JSQLParserException {
        if (oldWhere == null) {
            return CCJSqlParserUtil.parseCondExpression(newWhere);
        }
        String oldSegment = oldWhere.replaceAll(AND_SEGMENT + TMP_SEGMENT, "");
        String newSegment = newWhere.replaceAll(AND_SEGMENT + TMP_SEGMENT, "");
        return CCJSqlParserUtil.parseCondExpression(oldSegment + AND_SEGMENT + newSegment);
    }

    /**
     * 删除指定字段的where条件
     * @param where
     * @param columns
     * @return
     */
    public static Expression removeColumnsExpression(Expression where, Set<String> columns) {
        if (where == null) {
            return new EqualsTo(new LongValue(1), new LongValue(1));
        }
        if (where instanceof ComparisonOperator) {
            ComparisonOperator comparisonOperator = (ComparisonOperator) where;
            String columnName = ((Column) comparisonOperator.getLeftExpression()).getFullyQualifiedName();
            if (columns.contains(columnName)) {
                return new EqualsTo(new LongValue(1), new LongValue(1));
            }
        } else if (where instanceof InExpression) {
            InExpression inExpression = (InExpression) where;
            String columnName = ((Column) inExpression.getLeftExpression()).getFullyQualifiedName();
            if (columns.contains(columnName)) {
                return new EqualsTo(new LongValue(1), new LongValue(1));
            }
        } else if (where instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) where;
            binaryExpression.setLeftExpression(removeColumnsExpression(binaryExpression.getLeftExpression(), columns));
            binaryExpression.setRightExpression(removeColumnsExpression(binaryExpression.getRightExpression(), columns));
        }
        return where;
    }

    /**
     * 格式化参数
     * @param param
     * @return
     */
    public static String parseParam(Object param) {
        if (param == null) {
            return "";
        }
        Collection<?> col;
        if (param.getClass().isArray() || param instanceof Collection) {
            col = param.getClass().isArray() ? Arrays.asList(((Object[]) param)) : (Collection<?>) param;
        } else {
            col = Collections.singleton(param);
        }
        col = col.stream().map((node) -> "'" + ObjectUtil.toString(node) + "'").collect(Collectors.toList());
        return CollectionUtil.join(col, ",");
    }
}
